
package cassandrawindow;

/**
 * @author ADMIN
 */
import com.mysql.jdbc.CallableStatement;
import com.mysql.jdbc.PreparedStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.util.logging.Level;
import java.util.logging.Logger;

import java.util.Date;
import java.text.DateFormat;
import java.text.SimpleDateFormat;

import java.sql.Time;
import java.util.*;
import java.util.ArrayList;

public class MysqlDataManager67 {
    
    /*
        private Connection conn = null;

        private String TABLE_BANNERS        = "ox_banners";

        private String V3_BAN_DATE          = "v3_ban_date_cs";
        private String V3_CAM_DATE          = "v3_cam_date_cs";
        private String V3_CAM_HOURLY_CPC    = "v3_cam_hourly_cs";
        private String V3_CAM_IP            = "v3_cam_ip_cs";
        private String V3_DOMAIN_DATE       = "v3_domain_date_cs";
        private String V3_ZONE_DATE         = "v3_zone_date_cs";
        private String V3_BAN_HOURLY_TEST    = "v3_ban_hourly_cs";
        private String V3_CAM_HOURLY_TEST    = "v3_cam_hourly_cs";

        private String FRAUD_CLICK          = "fraud_click";
        private String BAN_PRO_TABLE        = "banlance_promotion";

        private String V3_BANNER_LOG        = "domain_banner_log";

        private String SYS_PARAMS_CS    = "000_sys_params_CS";
    */
        private Connection conn = null;        
        private String TABLE_BANNERS            = "ox_banners";
        
        private String V3_BAN_DATE              = "v3_ban_date";
        private String V3_CAM_DATE              = "v3_cam_date";
        private String V3_CAM_HOURLY_CPC        = "v3_cam_hourly";
        private String V3_CAM_IP                = "v3_cam_ip";
        private String V3_DOMAIN_DATE           = "v3_domain_date";
        private String V3_ZONE_DATE             = "v3_zone_date";
        private String V3_BAN_HOURLY_TEST       = "v3_ban_hourly";
        private String V3_CAM_HOURLY_TEST       = "v3_cam_hourly";

        private String FRAUD_CLICK              = "fraud_click";
        private String BAN_PRO_TABLE            = "banlance_promotion";

        private String V3_BANNER_LOG            = "domain_banner_log";
        private String SYS_PARAMS_CPC           = "000_sys_params_v4";
        private String SYS_PARAMS_CS            = "000_sys_params_CS";
    
    public MysqlDataManager67()
    {
        initConnection(true);
       
         /*try {
            ConnectionPool connPool = new ConnectionPool(ACCOUNT.URL_MYSQL_MAIN_67, ACCOUNT.username_mysql_main_67, ACCOUNT.pass_mysql_main_67);
            conn = connPool.getConnection();            
            Debug.println("Connected to MySQL Server 6.7 database");
            
        } catch (SQLException ex) {
            ex.printStackTrace();
            Debug.println("Couldn't connect to the MySQL database 6.7.  Make sure the MySQL connector jar is in the lib directory.  See the installation documentation for more information. ");
            Debug.println(ex.getMessage());
            Debug.println(ex.getMessage());
        }*/
    }

    public MysqlDataManager67(boolean autoCommit) {
        initConnection(autoCommit);
    }

    private void initConnection(boolean autoCommit) {
        try
       {
           Class.forName("com.mysql.jdbc.Driver").newInstance();            
           conn = DriverManager.getConnection(ACCOUNT.URL_MYSQL_MAIN_67 , ACCOUNT.username_mysql_main_67, ACCOUNT.pass_mysql_main_67);//abc!@#
           conn.setAutoCommit(autoCommit);
           //Debug.println("Connected to MySQL Server database");
       }
       catch (Exception ex)
       {
           Debug.println("Couldn't connect to the MySQL database 6.7.  Make sure the MySQL connector jar is in the lib directory.  See the installation documentation for more information. ");
           Debug.println(ex.getMessage());
       }
    }
    
    

    public void shutdownConnection()
    {
        try {
            conn.close();
            conn = null;            
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
        
        
    public List getAllCampaignId()
    {
        List infor = new ArrayList();
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("select campaignid from "+ TABLE_BANNERS);
            while (rs.next())
            {
                infor.add(rs.getString("campaignid"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 6.7.");
        }
        return infor;
    }

    public List getBannerIDByCampaignId(int campaignId)
    {
        List infor = new ArrayList();        
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("select bannerid from "+ TABLE_BANNERS +" where campaignid = "+campaignId+"");
            while (rs.next())
            {
                infor.add(rs.getString("bannerid"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 6.7.");
        }
        return infor;

    }

    public void insertDataBannerHour(String value)
    {
        try {
            Statement s = conn.createStatement();
            String sql = "replace into "+ V3_BAN_HOURLY_TEST +"(bannerid,clickhour,viewhour,dt) values "+ value;
            //Debug.println(sql);
            s.execute(sql);            
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        }
    }

    public void insertDataCampaignDay(String value)
    {
        try {
            Statement s = conn.createStatement();
            String sql = "replace into "+ V3_CAM_DATE +"(campaignid,totalclick,totalview,realclick,clickcharge,viewcharge,uv,uc,dt,ctr,moneyc,moneyv) values "+ value;
            //Debug.println(sql);
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        }        
    }

    public void insertDataCampainHour(String value)
    {
         try {
            Statement s = conn.createStatement();
            String sql = "replace into "+ V3_CAM_HOURLY_CPC +"(campaignid,h,clicks,views,dt) values "+ value;
            //Debug.println(sql);
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        }        
    }

    public void insertDataCampaignAndBanner(String campaignid,String bannerid,String totalclick,String totalview, String dt)
    {
        try {
            Statement s = conn.createStatement();
            String sql = "replace into "+ V3_BAN_DATE +" values (" + Integer.valueOf(campaignid) + ", " + Integer.valueOf(bannerid) +"," + Integer.valueOf(totalclick) +",0,0," + Integer.valueOf(totalview) +",0,0,0,0,0,0,0,'" + dt + "',0)";
            //Debug.println(sql);
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        }

    }

    public void insertSliceDataCampaignAndBanner(String value)
    {
        try {
            Statement s = conn.createStatement();
            String sql = "replace into "+ V3_BAN_DATE +"(campaignid,bannerid,totalclick,realclick,clickcharge,totalview,viewcharge,uc,uv,MB,MT,MN,Other,dt,money) values " + value;
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        }
    }

    public void insertDataCampaignIp(String value){
        try {
            Statement s = conn.createStatement();
            String sql = "replace into "+ V3_CAM_IP +"(campaignid, ip, dt) values "+ value ;
            //Debug.println(sql);
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        }
        
    }

    public void insertDataDomainData(String value)
    {
        try {
            Statement s = conn.createStatement();
            String sql = "replace into "+ V3_DOMAIN_DATE +"(campaignid, bannerid, domain, totalclick, realclick, totalview, viewcharge, domaincrc, partnerid, istarget, domaingroup, domaingroupcrc, dt, preoder, money) values "+ value ;
            //Debug.println(sql);
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        }

    }

    /*public void insertDataDomainData(String value)
    {
        String[] arrVal = new String[20];
        arrVal = value.split(",");
        
        try {
            //Statement s = conn.createStatement();
            String sql = "replace into "+ V3_DOMAIN_DATE +"(campaignid, bannerid, domain, totalclick, realclick, totalview, viewcharge, domaincrc, partnerid, istarget, domaingroup, domaingroupcrc, dt, preoder, money) "
                    + "values (?, ?, ?,?, ?, ?,?, ?, ?,?, ?, ?,?, ?, ?,?, ?, ?)";
            PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
            ps.setString(1, arrVal[0]);
            ps.setString(2, arrVal[1]);
            ps.setString(3, arrVal[2]);
            ps.setString(4, arrVal[3]);
            ps.setString(5, arrVal[4]);
            ps.setString(6, arrVal[5]);
            ps.setString(7, arrVal[6]);
            ps.setString(8, arrVal[7]);
            ps.setString(9, arrVal[8]);
            ps.setString(10, arrVal[9]);
            ps.setString(11, arrVal[10]);
            ps.setString(12, arrVal[11]);
            ps.setDate(13, java.sql.Date.valueOf(arrVal[12]));
            ps.setString(14, arrVal[13]);
            ps.setString(15, arrVal[14]);            
            //Debug.println(sql);            
            ps.execute(sql);
            ps.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        }

    }*/

    public void insertDataDomainDataBannerLog(String value)
    {
        try {
            Statement s = conn.createStatement();
            String sql = "replace into "+ V3_BANNER_LOG +"(bannerid, hang_domain, log_domain, hang_click, log_click, hang_view, log_view, dt) values "+ value ;
            //Debug.println(sql);
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        }

    }

    public void insertDataZone(String value)
    {
        try {
            Statement s = conn.createStatement();
            String sql = "replace into "+ V3_ZONE_DATE +"(campaignid, zoneid, bannerid, totalclick, realclick, clickcharge, totalview, viewcharge, dt, partnerid) values "+ value ;
            //Debug.println(sql);
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        }

    }

    public boolean checkExitsLineFraudClick(String line)
    {
        boolean retVal = false;
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("select * from "+ FRAUD_CLICK +" where line = "+line+"");
            if (rs.next())
            {
                retVal = true;
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 6.7.");
        }
        return retVal;
    }

    public void insertFraudClick(String value)
    {
        try {
            Statement s = conn.createStatement();
            String sql = "insert ignore into "+ FRAUD_CLICK +"(line, guidid, zoneid, campaingnid, bannerid, domain, status, created_date) values "+ value;
            //Debug.println(sql);
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        }
    }

    public int getCountFraudClickByBannerIDAndDay(int bannerId, String Day)
    {
        int retValue = 0;
             
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("select COUNT(*) from "+ FRAUD_CLICK +" where bannerid = " + bannerId + " AND created_date = '"+ Day +"' AND status = 0");            

            while (rs.next())
            {
                retValue = rs.getInt(1);                
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 6.7.");
        }
            
        return retValue;
    }

    public boolean updateFraudClick(int bannerId, String Day)
    {
        boolean check = false;
        int fraudClickCount = getCountFraudClickByBannerIDAndDay(bannerId, Day);
        Debug.printlnFaudClick("So fraukClick với bannerid "+ bannerId +" ngay " +Day +":"+fraudClickCount);
        //fraudClickCount = 100;
        try {
            Statement s = conn.createStatement();
            String str1 = "realclick = if((totalclick - "+ fraudClickCount+" ) > 0,totalclick - "+ fraudClickCount +" ,0)";
            String sql = "update  "+ V3_BAN_DATE +" set "+ str1 +" where bannerid = " + bannerId + " AND dt = '"+ Day +"'" ;
            check = s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        }

        return check;
    }

    public List getValidBanner()
    {
        List addInfo = new ArrayList();
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("select bannerid from "+ V3_BAN_DATE +" where totalclick  > 0");
            while (rs.next())
            {
                addInfo.add(rs.getString("bannerid"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 6.7.");
        }        
        return addInfo;
    }
    public Map getAllBannerHasDomain(String date)
    {
        Map<String,String> mp=new HashMap<String, String>();
        
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT bannerid,list_all_site FROM ox_banners WHERE DATE(updated) = "+ date +" ORDER BY bannerid DESC");
            while (rs.next())
            {
                mp.put(rs.getString("bannerid"), rs.getString("list_all_site"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 6.7.");
        }

        return mp;
    }

    public List getClickAndDomainByBannerid(String bannerid ,String date)
    {
        List CAD = new ArrayList<>();
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT domain,totalclick FROM "+V3_DOMAIN_DATE+" WHERE bannerid = "+ bannerid+" AND dt = '"+date+"'");            
            while (rs.next())
            {
                CAD.add(rs.getString("domain")+"_"+rs.getString("totalclick"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 6.7.");
        }

        return CAD;
    }

    public int getFraudClickByDomainAndBanner(String bannerid, String domain,String date)
    {
        int retValue = 0;
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("select COUNT(*) from "+ FRAUD_CLICK +" where bannerid = '"+bannerid+"' AND domain = '"+domain+"' AND created_date = '"+ date +"' AND status = 0");

            while (rs.next())
            {
                retValue = rs.getInt(1);
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 6.7.");
        }

        return retValue;
    }

    public Map getAllFraudClickByDay(String date)
    {
        Map<String, Integer> fraudclicks = new HashMap<>();
        
        int retValue = 0;
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT bannerid,domain,created_date, COUNT(*) FROM "+ FRAUD_CLICK +" WHERE created_date = '"+ date +"' GROUP BY bannerid,domain,created_date");

            while (rs.next())
            {
                String key = rs.getInt("bannerid")+"_"+rs.getString("domain")+"_"+date;
                fraudclicks.put(key,rs.getInt("COUNT(*)"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 6.7.");
        }

        return fraudclicks;
    }

    public Map getAllFraudClickByZoneDay(String date)
    {
        Map<String, Integer> fraudclicks = new HashMap<>();

        int retValue = 0;
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT bannerid,zoneid,campaingnid,created_date, COUNT(*) FROM "+ FRAUD_CLICK +" WHERE created_date = '"+ date +"' GROUP BY bannerid,zoneid,campaingnid,created_date");

            while (rs.next())
            {
                String key = rs.getInt("zoneid")+"_"+rs.getString("campaingnid")+"_"+rs.getString("bannerid")+"_"+date;
                fraudclicks.put(key,rs.getInt("COUNT(*)"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 6.7.");
        }

        return fraudclicks;
    }

    public Map getAllFraudClickByZone(String date)
    {
        Map<String, Integer> fraudclicks = new HashMap<>();

        int retValue = 0;
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT bannerid,zoneid,campaingnid,created_date, COUNT(*) FROM "+ FRAUD_CLICK +" WHERE created_date = '"+ date +"' GROUP BY bannerid,zoneid,campaingnid,created_date");

            while (rs.next())
            {
                String key = rs.getInt("bannerid")+"_"+rs.getString("campaingnid")+"_"+rs.getString("zoneid")+"_"+date;
                fraudclicks.put(key,rs.getInt("COUNT(*)"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 6.7.");
        }

        return fraudclicks;
    }

    public Map getAllFraudClickByBannerid(String date)
    {
        Map<String, Integer> fraudclicks = new HashMap<>();
        
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT bannerid,created_date, COUNT(*) FROM "+ FRAUD_CLICK +" WHERE created_date = '"+ date +"' GROUP BY bannerid,created_date");

            while (rs.next())
            {
                String key = rs.getInt("bannerid")+"_"+date;
                fraudclicks.put(key,rs.getInt("COUNT(*)"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 6.7.");
        }

        return fraudclicks;
    }

    public Map getAllFraudClickByCampaignid(String date)
    {
        Map<String, Integer> fraudclicks = new HashMap<>();
        
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT campaingnid,created_date, COUNT(*) FROM "+ FRAUD_CLICK +" WHERE created_date = '"+ date +"' GROUP BY campaingnid,created_date");

            while (rs.next())
            {
                String key = rs.getInt("campaingnid")+"_"+date;
                fraudclicks.put(key,rs.getInt("COUNT(*)"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 6.7.");
        }

        return fraudclicks;
    }

    public Map getMoneyByBannerId(String date)
    {
        Map<String, Long> MoneyByBanner = new HashMap<>();
        
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery(" SELECT bannerid,SUM(money) FROM "+ V3_DOMAIN_DATE +" WHERE dt = '"+ date +"' GROUP BY bannerid,dt");

            while (rs.next())
            {
                MoneyByBanner.put(rs.getString("bannerid"), rs.getLong("SUM(money)"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 6.7.");
        }
        return MoneyByBanner;
    }
    public Map getMoneyByCampaignIdAndDomain(String date)
    {
            Map<String, String> MoneyByBanner = new HashMap<>();

            try
            {
                Statement st= (Statement)conn.createStatement();
                ResultSet rs = st.executeQuery("SELECT d.campaignid, d.domain, sum(realclick), sum(money), sum(totalview) FROM v3_domain_date d WHERE dt = '"+ date +"' GROUP BY d.campaignid ,d.domain");
                //Debug.println("SELECT d.campaignid, d.domain, sum(totalclick), sum(money) FROM v3_domain_date d WHERE dt = '"+ date +"' AND totalclick > 0 GROUP BY d.campaignid ,d.domain");
                while (rs.next())
                {
                    MoneyByBanner.put(rs.getString("d.campaignid")+"_"+rs.getString("d.domain"),rs.getString("sum(money)")+"_"+rs.getString("sum(realclick)")+"_"+rs.getString("sum(totalview)"));
                }
                rs.close();
                st.close();
            }
            catch (Exception ex)
            {
                Debug.println(ex.getMessage());
                Debug.println("Couldn't connect to the MySQL database 6.7.");
            }
            return MoneyByBanner;
    }

    public Map getClickByDomainDate(String date)
    {
         Map<String, Long> ClickByDomainDate = new HashMap<>();

        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery(" SELECT bannerid,domain,totalclick FROM v3_domain_date WHERE dt = '"+ date +"' GROUP BY bannerid,dt");

            while (rs.next())
            {
                if(rs.getLong("totalclick") >0)
                {
                    ClickByDomainDate.put(rs.getString("bannerid")+"_"+rs.getString("domain")+"_"+date, rs.getLong("totalclick"));
                }
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 6.7.");
        }
        return ClickByDomainDate;
    }

    public Map getClickByCampaignid(String date)
    {
        Map<String, String> ClickAndMoneyByCamp = new HashMap<>();
         
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery(" SELECT campaignid,SUM(realclick) AS sumrealclick,SUM(money) AS money FROM "+ V3_DOMAIN_DATE +" WHERE dt = '"+ date +"' GROUP BY campaignid,dt");

            while (rs.next())
            {
                //if(rs.getLong("sumrealclick") > 0)
                //{
                    ClickAndMoneyByCamp.put(rs.getString("campaignid")+"_"+date, rs.getLong("sumrealclick")+"_"+rs.getLong("money"));
                //}
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 6.7.");
        }
        return ClickAndMoneyByCamp;
    }

    public void insertBalanceAndPromotionMoney(String value)
    {
        try {
            Statement s = conn.createStatement();
            String sql = "replace into "+ BAN_PRO_TABLE +"(userid,campaignid,domain,totalclick,totalview,balance,promotions,groupid,dt) values " + value;
            //Debug.println(sql);
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        }
    }

    public Map getSimilityByID(long NewsID)
    {
        Map<Long, String> map = new HashMap<>();
        try
        {
                Statement s = conn.createStatement();
                String sql = "SELECT uid,GROUP_CONCAT(cmp) FROM ( SELECT DISTINCT C.campaignid AS cmp, A.uid AS uid Where    GROUP BY uid";
                ResultSet rs = s.executeQuery(sql);
                while (rs.next())
                {
                    map.put(rs.getLong("uid"),rs.getString("GROUP_CONCAT(cmp)"));
                }
                rs.close();
                s.close();
         }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 6.7.");
        }
        return map;
    }

    public void getNewsIDbyCat(int CatID,int limit,List newsList)
    {        
        try
        {
            Statement s = conn.createStatement();
            String sql = "SELECT NewsId FROM NewsFeaturedRecomment WHERE GeneralCatID = "+ CatID +" ORDER BY id desc limit "+limit;
            //Debug.println(sql);
            ResultSet rs = s.executeQuery(sql);
            while (rs.next())
            {
                newsList.add(rs.getLong("NewsId"));
            }
            rs.close();
            s.close();
         }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 6.7.");
        }        
    }

    public void getAllsimilityByNewsID(long NewsID,List infor)
    {
        //List infor = new ArrayList();
        try
        {
            Statement s = conn.createStatement();
            String sql = "SELECT second FROM bigdata_pair_simi where first = "+NewsID;
            ResultSet rs = s.executeQuery(sql);
            while (rs.next())
            {
                infor.add(rs.getLong("second"));
            }
            rs.close();
            s.close();
         }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 6.7.");
        }        
    }
    

    public Map getRankChangeByTime(int cat,int limit)
    {
         Map<Long, Double> map = new HashMap<>();
            try
            {
                Statement s = conn.createStatement();
                String sql = "SELECT NewsId,Rank FROM NewsScoreRecomment WHERE DATE(CreatedDate) = CURDATE() and GeneralCatID = "+cat+" order by CreatedDate desc limit "+limit;
                Debug.println(sql);
                ResultSet rs = s.executeQuery(sql);
                while (rs.next())
                {
                    map.put(rs.getLong("NewsId"),rs.getDouble("Rank"));
                }
                rs.close();
                s.close();
             }
            catch (Exception ex)
            {
                Debug.println(ex.getMessage());
                Debug.println("Couldn't connect to the MySQL database 6.7.");
            }
         return map;
    }

    public boolean checkVietNamNet(long NewsId)
    {
            boolean retVal = false;
            try
            {
                Statement s = conn.createStatement();
                String sql = "SELECT * FROM ViewForAdmicro WHERE NewsUrl LIKE '%vietnamnet%' AND NewsID = "+NewsId;
                ResultSet rs = s.executeQuery(sql);
                while (rs.next())
                {
                    retVal = true;
                }
                rs.close();
                s.close();
             }
            catch (Exception ex)
            {
                Debug.println(ex.getMessage());
                Debug.println("Couldn't connect to the MySQL database 6.7.");
            }

            return retVal;
    }

    public void insertDataCampainHourForCPC(String value)
    {
         try {
            Statement s = conn.createStatement();
            String sql = "replace into "+ V3_CAM_HOURLY_TEST +"(campaignid,h,clicks,views,dt) values "+ value;
            //Debug.println(sql);
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        }
    }

    public Map getAllBannerRunning(String date)
    {
        Map<String,String> mp=new HashMap<>();
        try
        {
            Statement st= (Statement)conn.createStatement();
            //ResultSet rs = st.executeQuery("SELECT bannerid,list_all_site FROM ox_banners WHERE DATE(updated) = '"+ date +"' ORDER BY bannerid DESC");
            ResultSet rs = st.executeQuery("SELECT bannerid ,GROUP_CONCAT(domain) AS list_site,SUM(totalclick) AS totalclick,SUM(totalview) AS totalview FROM v3_domain_date WHERE dt ='"+date+"' GROUP BY bannerid ");
            //Debug.println("SELECT bannerid ,GROUP_CONCAT(domain) AS domain,SUM(totalclick) AS totalclick,SUM(totalview) AS totalview FROM v3_domain_date WHERE dt ='"+date+"' GROUP BY bannerid");
            while (rs.next())
            {
                mp.put(rs.getString("bannerid"), rs.getString("list_site")+"_"+rs.getString("totalclick")+"_"+rs.getString("totalview")+"_"+date);
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 6.7.");
        }

        return mp;
    }

    public String getDetailBannerByDomain(String bannerid ,String date,String domainSlice)
    {
        String retValue = "";
        try
        {
            Statement st= (Statement)conn.createStatement();           
            ResultSet rs = st.executeQuery("SELECT bannerid ,SUM(totalclick) AS totalclick,SUM(totalview) AS totalview "
                                                + "FROM v3_domain_date WHERE dt ='"+date+"' AND bannerid = '"+bannerid+"' "
                                                + "AND domain IN ("+domainSlice+") GROUP BY bannerid ");
            
            /*Debug.println("SELECT bannerid ,SUM(totalclick) AS totalclick,SUM(totalview) AS totalview "
                                                + "FROM v3_domain_date WHERE dt ='"+date+"' AND bannerid = '"+bannerid+"' "
                                                + "AND domain IN ("+domainSlice+") GROUP BY bannerid ");*/

            while (rs.next())
            {
               retValue =  rs.getString("totalclick")+"_"+rs.getString("totalview");
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 6.7.");
        }

        return retValue;
    }

    public Map getClickByDomainDateCPCandCPCplus(String date)
    {
        Map<String, String> ClickByDomainDate = new HashMap<>();
        
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT bannerid,SUM(totalclick) AS totalclick,SUM(totalview) AS totalview,SUM(money) AS money FROM "+V3_DOMAIN_DATE+" WHERE dt = '"+date+"' GROUP BY bannerid,dt");
            //Debug.println("SELECT campaignid,bannerid,SUM(totalclick) AS totalclick,SUM(totalview) AS totalview,SUM(money) AS money FROM "+V3_DOMAIN_DATE_VMASK+" WHERE dt = '"+date+"' GROUP BY campaignid ,bannerid,dt");
            while (rs.next())
            {
                ClickByDomainDate.put(rs.getString("bannerid")+"_"+date, rs.getLong("totalclick")+"_"+rs.getLong("totalview")+"_"+rs.getLong("money"));
            }

            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 6.7.");
        }
        return ClickByDomainDate;
    }

    public Map getAllCampaignByDate(String date)
    {
        Map<String, String> allCampaignid = new HashMap<>();        
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT campaignid,SUM(totalview) AS totalview,SUM(totalclick) AS totalclick,SUM(realclick) AS realclick,SUM(clickcharge) AS clickcharge,SUM(money) AS money,dt FROM "+ V3_BAN_DATE +" where dt ='"+ date +"' GROUP BY campaignid,dt");
            while (rs.next())
            {
                allCampaignid.put(rs.getString("campaignid")+"_"+rs.getString("dt"), rs.getLong("totalview")+"_"+rs.getLong("totalclick")+"_"+rs.getLong("realclick")+"_"+rs.getLong("clickcharge")+"_"+rs.getLong("money"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 6.7.");
        }

        return allCampaignid;
    }

    public void insertDataCampainTemp(String value)
    {
        try {
            Statement s = conn.createStatement();
            String sql = "replace into v3_cam_date (campaignid,totalclick,totalview,realclick,clickcharge,viewcharge,uv,uc,dt,ctr,moneyc,moneyv) values "+ value;
            Debug.println(sql);

            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        }
    }

    public void insertDataZoneCPC(String value)
    {
        try {

            Statement s = conn.createStatement();
            String sql = "replace into "+ V3_ZONE_DATE +"(campaignid, zoneid, bannerid, totalclick, realclick,clickcharge,totalview, viewcharge, dt, partnerid) values "+ value ;
            //Debug.println(sql);
            s.execute(sql);
            s.close();

        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        }
    }

     public void loadToV3DomainDateFromFile(String filePath) throws Exception {
        Statement s = null;
        ResultSet resultSet = null;

        try {            
            String query = "LOAD DATA LOCAL INFILE '" + filePath + "'" +
                " INTO TABLE "+V3_DOMAIN_DATE+" FIELDS TERMINATED BY ',' " +
                "LINES TERMINATED BY ',\\n' (campaignid, bannerid,domain," +
                " totalclick,realclick,totalview,viewcharge, @domaincrcRaw," +
                "partnerid,istarget,domaingroup,@domaingroupRaw,@dtStr,preoder,money)" +
                " SET domaingroupcrc=crc32(@domaingroupRaw), dt=date(@dtStr)," +
                " domaincrc=crc32(@domaincrcRaw)";
            Debug.println(query);
            s = conn.createStatement();            
            resultSet = s.executeQuery(query);
            conn.commit();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        } finally {
            if (resultSet != null) {
                try {
                  resultSet.close();
                } catch (SQLException e) {
                } // nothing we can do
            }
            if (s != null) {
                try {
                  s.close();
                } catch (SQLException e) {
                } // nothing we can do
            }
         }
    }
     
    public void loadToV3BanDateFromFile(String filePath) throws Exception {

        try {
            String query = "LOAD DATA LOCAL INFILE '" + filePath + "'" +
                " INTO TABLE "+ V3_BAN_DATE +" FIELDS TERMINATED BY ',' " +
                "LINES TERMINATED BY ',\\n' (campaignid, bannerid," +
                "totalclick,realclick,clickcharge,totalview,viewcharge,uc," +
                "uv,MB,MT,MN,Other,dt,money)";
            Debug.println(query);
            Statement s = conn.createStatement();
            s.executeQuery(query);
            conn.commit();
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        }
    }

    public void loadToV3CamDateFromFile(String filePath) throws Exception {

        try {
            String query = "LOAD DATA LOCAL INFILE '" + filePath + "'" +
                " INTO TABLE "+ V3_CAM_DATE +" FIELDS TERMINATED BY ',' " +
                "LINES TERMINATED BY ',\\n' (campaignid, totalclick," +
                "totalview,realclick,clickcharge,viewcharge,uv,uc," +
                "dt,ctr,moneyc,moneyv)";
            Debug.println(query);
            Statement s = conn.createStatement();
            s.executeQuery(query);
            conn.commit();
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        }
    }

     public void loadToV3CamHourlyFromFile(String filePath) throws Exception {

        try {
            String query = "LOAD DATA LOCAL INFILE '" + filePath + "'" +
                " INTO TABLE "+V3_CAM_HOURLY_CPC+" FIELDS TERMINATED BY ',' " +
                "LINES TERMINATED BY ',\\n' (campaignid, h," +
                "clicks,views," +
                "dt)";
            Debug.println(query);
            Statement s = conn.createStatement();
            s.executeQuery(query);
            conn.commit();
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        }
    }

     public void loadToV3BannerHourlyFromFile(String filePath) throws Exception {

        try {
            String query = "LOAD DATA LOCAL INFILE '" + filePath + "'" +
                " INTO TABLE "+ V3_BAN_HOURLY_TEST +" FIELDS TERMINATED BY ';' " +
                "LINES TERMINATED BY ';\n' (bannerid, clickhour," +
                "viewhour," +
                "dt)";
            Debug.println(query);
            Statement s = conn.createStatement();
            s.executeQuery(query);
            conn.commit();
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        }
    }

     public void loadToV3ZoneFromFile(String filePath) throws Exception {
     
        try {
            String query = "LOAD DATA LOCAL INFILE '" + filePath + "'" +
                " INTO TABLE "+ V3_ZONE_DATE +" FIELDS TERMINATED BY ',' " +
                "LINES TERMINATED BY ',\\n' (campaignid, zoneid," +
                "bannerid,totalclick,realclick,clickcharge,totalview,viewcharge," +
                "dt,partnerid)";
            Debug.println(query);
            Statement s = conn.createStatement();
            s.executeQuery(query);
            conn.commit();
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        }
    }
     
    public void loadToV3CamIpFromFile(String filePath) throws Exception {     
        try {
            String query = "LOAD DATA LOCAL INFILE '" + filePath + "'" +
                " INTO TABLE "+ V3_CAM_IP +" FIELDS TERMINATED BY ',' " +
                "LINES TERMINATED BY ',\\n' (campaignid, ip," +                
                "dt)";
            Debug.println(query);
            Statement s = conn.createStatement();
            s.executeQuery(query);
            conn.commit();
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        }
    } 

    public void addPartition(String tablename,String currDate){        
        try {                  
            java.sql.CallableStatement stmt = conn.prepareCall("{call v3_partition_add_CS (?)}");
            // Defines all the required parameter values.           
            stmt.setString(1,tablename);
            stmt.execute();
            stmt.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        }
    }
    
    public void dropPartition(String tablename,String currDate){        
        try {          
            java.sql.CallableStatement stmt = conn.prepareCall("{call v3_partition_pre_insert_CS (?,?)}");
            //java.sql.CallableStatement stmt = conn.prepareCall("{call v3_partition_pre_insert_v1 (?,?)}");
            // Defines all the required parameter values.
            stmt.setString(1,currDate);
            stmt.setString(2,tablename);
            stmt.execute();
            stmt.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        }
    }

    public void mergePartition(String tablename,String revenuetype){
        try {
            java.sql.CallableStatement stmt = conn.prepareCall("{call v4_partition_merger_CS (?,?)}");
            // Defines all the required parameter values.
            stmt.setString(1,tablename);
            stmt.setString(2,revenuetype);
            stmt.execute();
            stmt.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        }
    }


    public void updateLastTimeCalcul()
    {

        try {
            Statement s = conn.createStatement();
            String str1 = "LastTimeCalc = '"+TimeUtil.getCurrentTimeInStringForLog()+"'";
            String sql = "update  "+ SYS_PARAMS_CS +" set "+ str1 +" where id = 1";
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        }
    }
    
    public void updateLastTimeCalculForMoney()
    {

        try {
            Statement s = conn.createStatement();
            String str1 = "LastTimeCalc_cpc = '"+TimeUtil.getCurrentTimeInStringForLog()+"'";
            String sql = "update  "+ SYS_PARAMS_CPC +" set "+ str1 +" where id = 1";
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        }
    }

    public void updateSysDateCPC()
    {
        try {
            Statement s = conn.createStatement();
            String str1 = "sys_date_cpc = '"+TimeUtil.getCurrentTimeInString()+"'";
            String sql = "update  "+ SYS_PARAMS_CS +" set "+ str1 +" where id = 1";
            Debug.println(sql);
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        }
    }
    
    public void updateSysDateCPCForMoney()
    {
        try {
            Statement s = conn.createStatement();
            String str1 = "sys_date_cpc = '"+TimeUtil.getCurrentTimeInString()+"'";
            String sql = "update  "+ SYS_PARAMS_CPC +" set "+ str1 +" where id = 1";
            Debug.println(sql);
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        }
    }

    public String getSysDateCPC()
    {
        String retStr = "";
        try
        {
            Statement s = conn.createStatement();
            String sql = "SELECT sys_date_cpc FROM "+ SYS_PARAMS_CS +" where id = 1";
            ResultSet rs = s.executeQuery(sql);
            if (rs.next())
            {
                retStr = rs.getString("sys_date_cpc");
            }
            rs.close();
            s.close();
         }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 6.7.");
        }
        return retStr;
    }

    public void getOutFileAnalytic(String filePath){        
        try {          
            java.sql.CallableStatement stmt = conn.prepareCall("{call v4_nb_report_by_site_v2_outfile (?)}");
            //java.sql.CallableStatement stmt = conn.prepareCall("{call v3_partition_pre_insert_v1 (?,?)}");
            // Defines all the required parameter values.
            stmt.setString(1,filePath);
            //stmt.setString(2,tablename);
            stmt.execute();
            stmt.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        }
    }
    
    public void loadToV3NbReportBySite(String filePath) throws Exception {

        try {
            String query = "LOAD DATA LOCAL INFILE '" + filePath + "'" +
                " INTO TABLE "+V3_CAM_HOURLY_CPC+")";
            Debug.println(query);
            Statement s = conn.createStatement();
            s.executeQuery(query);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        }
    }
    
    public void beforeLoadData() throws Exception {

        try {
            String query = "SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE";
            Debug.println(query);
            Statement s = conn.createStatement();
            s.executeQuery(query);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        }
    }
    
    public Map getUvUcByBannerid(String date)
    {
        Map<Integer, String> uv_uc = new HashMap<>();

        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT bannerid,uc,uv FROM vmask_cpc_banner_uv_uc where dt ='"+date+"'");
            //Debug.println("SELECT bannerid,uc,uv FROM cpc_ban_date_uniq");
            while (rs.next())
            {
                uv_uc.put(rs.getInt("bannerid"), rs.getString("uc")+"_"+rs.getString("uv"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 6.7. ");
        }

        return uv_uc;
    }
    
    public Map getUvUcByCampaignid(String date)
    {
        Map<Integer, String> uv_uc = new HashMap<>();

        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT campaignid,uc,uv FROM vmask_cpc_campaign_uv_uc where dt ='"+date+"'");
            //Debug.println("SELECT campaignid,uc,uv FROM cpc_cam_date_uniq");
            while (rs.next())
            {
                uv_uc.put(rs.getInt("campaignid"), rs.getString("uc")+"_"+rs.getString("uv"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 6.7. ");
        }

        return uv_uc;
    }
    
    
    public int getTotalClickBy5H(String date)
    {
        int retVal = 0;

        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT SUM(clicks) AS clicks FROM v3_cam_hourly WHERE h ='5' AND dt ='"+date+"'");
            //Debug.println("SELECT campaignid,uc,uv FROM cpc_cam_date_uniq");
            if (rs.next())
            {
                retVal = rs.getInt("clicks");
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 6.7. ");
        }

        return retVal;
    }
    
    public Map getBannerIDandCapmIDByCPCandCPCPlus()
    {
        Map<String, String> banAndCamp = new HashMap<>();

        try
        {
            Statement st= (Statement)conn.createStatement();
            // status =(1.8) là dang chay
            //(5,10) la cpc và cpc plus                        
            String sql ="SELECT B.bannerid,A.campaignid "
                    + "FROM `ox_campaigns` A "
                    + "INNER JOIN `ox_banners` B "
                    + "ON A.campaignid = B.campaignid  "
                    + "WHERE (A.`revenue_type` = 10 OR A.`revenue_type`= 5 ) AND A.`isExpired` IN (0,3,4,5,10,11) "
                    + "AND CURRENT_DATE BETWEEN B.`activate` AND DATE_ADD(A.`expire`,INTERVAL 1 DAY) "
                    + "ORDER BY B.bannerid DESC";
            
            Debug.println(sql);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next())
            {
                banAndCamp.put(rs.getString("B.bannerid"),rs.getString("A.campaignid"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }
        return banAndCamp;
    }
    
    public List getBannerIDByCPCandCPCPlus()
    {
        List infor = new ArrayList();
        try
        {
            Statement st= (Statement)conn.createStatement();
            // status =(1.5) là dang chay va dung
            //(5,10) la cpc và cpc plus
            /*String sql ="SELECT B.bannerid "
                    + "FROM `ox_campaigns` A "
                    + "INNER JOIN `ox_banners` B "
                    + "ON A.campaignid = B.campaignid  "
                    + "WHERE (A.`revenue_type` = 10 OR A.`revenue_type`=5) AND A.`isExpired` NOT IN (1, 2, 3) "
                    + "AND CURRENT_DATE BETWEEN B.`activate` AND B.`expire` AND B.`status` = 1 AND B.`deleted` = 0 "
                    + "ORDER BY B.bannerid DESC";*/
            //1036338 :VIDEO boxapp                    
            String sql ="SELECT B.bannerid "
                    + "FROM `ox_campaigns` A "
                    + "INNER JOIN `ox_banners` B "
                    + "ON A.campaignid = B.campaignid  "
                    + "WHERE (A.`revenue_type` = 10 OR A.`revenue_type`=5 ) "
                    + "AND CURRENT_DATE BETWEEN B.`activate` AND DATE_ADD(B.`expire`,INTERVAL 1 DAY) AND B.`status` IN (1,2) "
                    //+ "AND CURRENT_DATE BETWEEN B.`activate` AND B.`expire` "
                    + "ORDER BY B.bannerid DESC";
             
            Debug.println(sql);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next())
            {
                infor.add(rs.getString("B.bannerid"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }
        return infor;
    }
    
    public Map getMaxValueDaybyBannerIdCPC(String date)
    {
        Map<Integer, Long> MaxValue = new HashMap<>();
        try
        {
            Statement st= (Statement)conn.createStatement();
            String sql = "SELECT DISTINCT A.campaignid, A.maxValueDay FROM `ox_campaigns` A "+
                    //"INNER JOIN `ox_banners` C ON A.`campaignid` =C.`campaignid`  "+
                    "WHERE A.`revenue_type` IN(5,10,0)";
            Debug.println(sql);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next())
            {
                MaxValue.put(rs.getInt("A.campaignid"), rs.getLong("A.maxValueDay"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }
        return MaxValue;
    }
    
    public List getCampaignCPCandCPCPLUS() throws InterruptedException
    {
            List CampaignCPC = new ArrayList();
            try
            {
                Statement st= (Statement)conn.createStatement();
                
                //1036338 la box AF
                String sql = "SELECT "+
                        "DISTINCT (A.`campaignid`) "+
                        "FROM "+
                        "`ox_campaigns` A " +
                        "WHERE (A.`revenue_type` = 10 OR A.`revenue_type`=5 OR A.campaignid = 1036338) "+
                        //"AND A.`isExpired` NOT IN (1, 2, 3) "+
                        "AND CURRENT_DATE BETWEEN A.`activate`"+
                        "AND DATE_ADD(A.`expire`,INTERVAL 1 DAY) ";                
                Debug.println(sql);

                ResultSet rs = st.executeQuery(sql);
                while (rs.next())
                {
                    CampaignCPC.add(rs.getString("campaignid"));
                }
                rs.close();
                st.close();
            }
            catch (Exception ex)
            {
                Debug.println(ex.getMessage());
                Debug.println("Couldn't connect to the MySQL database 5.9. ");
            }

            Thread.sleep(10);
        //}
        return CampaignCPC;
    }
    
    public HashSet getDataPauseCpc(String date){
        
        HashSet hs=new HashSet();
        try
        {
            Statement st= (Statement)conn.createStatement();
            String sql = "SELECT A.campaignid,B.bannerid FROM `ox_campaigns` A  "                    
                    + "INNER JOIN `ox_banners` B "
                    + "ON A.campaignid = B.campaignid  "
                    + "WHERE (A.`revenue_type` = 10 OR A.`revenue_type`=5 ) AND A.status = 5 "
                    + "AND CURRENT_DATE BETWEEN B.`activate` AND DATE_ADD(A.`expire`,INTERVAL 1 DAY)";
            Debug.println(sql);
            ResultSet rs = st.executeQuery(sql);
            
            while (rs.next())
            {
                hs.add(rs.getString("A.campaignid"));
                hs.add(rs.getString("B.bannerid"));
            }
            st.close();
            rs.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }

        return hs;        
    }
    
    
    public HashSet getDataPauseCpcByDay(String date){
        
        HashSet hs = new HashSet();
        try
        {
            Statement st = (Statement)conn.createStatement();
            //ResultSet rs = st.executeQuery("SELECT bannerid,list_all_site FROM ox_banners WHERE DATE(updated) = '"+ date +"' ORDER BY bannerid DESC");
            ResultSet rs = st.executeQuery("SELECT id FROM selfserving_inform  "                                       
                    + "WHERE DATE = '"+date+"' and type IN(4,10)");
            
            while (rs.next())
            {
                hs.add(rs.getString("id"));                
            }
            st.close();
            rs.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }

        return hs;        
    }
    
    
    public HashSet getDataChangeCpcByDay(String date){
        
        HashSet hs = new HashSet();
        try
        {
            Statement st = (Statement)conn.createStatement();
            //ResultSet rs = st.executeQuery("SELECT bannerid,list_all_site FROM ox_banners WHERE DATE(updated) = '"+ date +"' ORDER BY bannerid DESC");
            ResultSet rs = st.executeQuery("SELECT id FROM selfserving_inform  "                                       
                    + "WHERE DATE = '"+date+"' and type IN(13)");
            
            while (rs.next())
            {
                hs.add(rs.getString("id"));                
            }
            st.close();
            rs.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 6.7. ");
        }

        return hs;        
    }
    
    
    public Map getPriceByDomainCPC()
    {
        Map<String, Integer> PriceByDomain = new HashMap<>();

        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT name,price FROM `selfserving_price` A WHERE A.`type` IN(1)");

            while (rs.next())
            {
                PriceByDomain.put(rs.getString("name"), rs.getInt("price"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }
        return PriceByDomain;
    }
    
    public Map getBannerIDandCapmIDByCPCandCPCPlusSoloEnbac()
    {
        Map<String, String> banAndCamp = new HashMap<>();
        try
        {
            Statement st= (Statement)conn.createStatement();          
            String sql ="SELECT B.bannerid,A.campaignid "
                    + "FROM `ox_campaigns` A "
                    + "INNER JOIN `ox_banners` B "
                    + "ON A.campaignid = B.campaignid "
                    + "WHERE (A.campaignid = '1021159' OR A.campaignid = '1021653') "                    
                    + "ORDER BY B.bannerid DESC";
            Debug.println(sql);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next())
            {
                banAndCamp.put(rs.getString("B.bannerid"),rs.getString("A.campaignid"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }
        return banAndCamp;
    }
    
    public Map getBannerIDandCapmIDByBoxvideoAF()
    {
        Map<String, String> banAndCamp = new HashMap<>();
        try
        {
            Statement st= (Statement)conn.createStatement();          
            String sql ="SELECT B.bannerid,A.campaignid "
                    + "FROM `ox_campaigns` A "
                    + "INNER JOIN `ox_banners` B "
                    + "ON A.campaignid = B.campaignid "
                    + "WHERE (A.campaignid = '1036338') "                    
                    + "ORDER BY B.bannerid DESC";
            Debug.println(sql);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next())
            {
                banAndCamp.put(rs.getString("B.bannerid"),rs.getString("A.campaignid"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }
        return banAndCamp;
    }
    
    
    
    public List getCampaignCPCPlus() throws InterruptedException
    {
            List CampaignCPCPlus = new ArrayList();
            try
            {
                Statement st= (Statement)conn.createStatement();
                String sql = "SELECT campaignid "+
                                "FROM `ox_campaigns` "+
                                "WHERE revenue_type IN (10)";
                Debug.println(sql);
                ResultSet rs = st.executeQuery(sql);
                while (rs.next())
                {
                    CampaignCPCPlus.add(rs.getLong("campaignid"));
                }
                rs.close();
                st.close();
            }
            catch (Exception ex)
            {
                Debug.println(ex.getMessage());
                Debug.println("Couldn't connect to the MySQL database 5.9. ");
            }

            Thread.sleep(10);
        //}
        return CampaignCPCPlus;
    }
    
    public Map getPriceByDomain()
    {        
        Map<String, Integer> PriceByDomain = new HashMap<>();
        
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT name, price FROM selfserving_price A WHERE A.`type`=1");

            while (rs.next())
            {
                PriceByDomain.put(rs.getString("name"), rs.getInt("price"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }
        return PriceByDomain;
    }
    
    public Map getPriceByDomainCPCPlus()
    {
        Map<String, Integer> PriceByDomain = new HashMap<>();

        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT name, price FROM selfserving_price A WHERE A.`type` IN(5)");

            while (rs.next())
            {
                PriceByDomain.put(rs.getString("name"), rs.getInt("price"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }
        return PriceByDomain;
    }
    
    public Map getAllDomainByBannerid(){
        Map<String,String> mapDomain= new HashMap<String,String>();
        
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT B.bannerid,B.list_site "
                    + "FROM `ox_campaigns` A "
                    + "INNER JOIN `ox_banners` B "
                    + "ON A.campaignid = B.campaignid "
                    + "WHERE (A.`revenue_type` = 10 OR A.`revenue_type`=5 ) AND  B.`status` IN (1,5) "
                    + "AND CURRENT_DATE BETWEEN B.`activate` AND DATE_ADD(A.`expire`,INTERVAL 1 DAY) "
                    + "ORDER BY B.bannerid DESC ");

            while (rs.next())
            {                
                mapDomain.put(rs.getString("B.bannerid"), rs.getString("B.list_site"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }
        return mapDomain;
    }
    
    public Map getAllGeoByBanner(){
        
        Map<String,String> geoBanner= new HashMap<String,String>();
        
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT B.bannerid,B.locationAd,B.list_citys "
                    + "FROM `ox_campaigns` A "
                    + "INNER JOIN `ox_banners` B "
                    + "ON A.campaignid = B.campaignid "
                    + "WHERE (A.`revenue_type` = 10 OR A.`revenue_type`=5 ) AND  B.`status` IN (1,5) "
                    + "AND CURRENT_DATE BETWEEN B.`activate` AND DATE_ADD(B.`expire`,INTERVAL 1 DAY) "
                    + "AND locationAd != '1,2,3' ");

            while (rs.next())
            {                
                geoBanner.put(rs.getString("B.bannerid"), rs.getString("B.locationAd")+ "_" + rs.getString("B.list_citys"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }
        return geoBanner;        
        
    }
    
    public Map mapCityWithGeo(){
        
        Map<String,String> cityGeo= new HashMap<String,String>();
        
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("select cityid,locationid from selfserving_citys");

            while (rs.next())
            {                
                cityGeo.put(rs.getString("cityid"), rs.getString("locationid"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }
        return cityGeo;                
    }
    
    public List getCampaignOutBudget() throws InterruptedException
    {
            List CampaignCPC = new ArrayList();
            try
            {
                Statement st= (Statement)conn.createStatement();
                String sql = "SELECT DISTINCT (A.campaignid) FROM selfserving_users B "+
                            "INNER JOIN ox_campaigns A ON B.user_id = A.uid "+
                            "WHERE A.`revenue_type`= 5 AND A.`isExpired` IN (0,3,4,5,10,11) " +                       
                            "AND CURRENT_DATE BETWEEN A.`activate` AND DATE_ADD(A.`expire`,INTERVAL 1 DAY) AND ((B.balance + B.promotions+ B.overdraft) < 0)";
                
                Debug.println(sql);
                ResultSet rs = st.executeQuery(sql);
                while (rs.next())
                {
                    CampaignCPC.add(rs.getString("campaignid"));
                }
                rs.close();
                st.close();
            }
            catch (Exception ex)
            {
                Debug.println(ex.getMessage());
                Debug.println("Couldn't connect to the MySQL database 5.9. ");
            }
           
        
        return CampaignCPC;
    }

    
    public List getCampaignOutBudgetPlus() throws InterruptedException
    {
            List CampaignCPC = new ArrayList();
            try
            {
                Statement st= (Statement)conn.createStatement();
                String sql = "SELECT DISTINCT (A.campaignid) FROM cpcplus_users_balances B "+
                            "INNER JOIN ox_campaigns A ON B.user_id = A.uid "+
                            "WHERE A.`revenue_type`= 10 AND A.`isExpired` IN (0,3,4,5,10,11) " +                       
                            "AND CURRENT_DATE BETWEEN A.`activate` AND DATE_ADD(A.`expire`,INTERVAL 1 DAY) AND ((B.balance + B.promotions+ B.overdraft) < 0)";
                
                Debug.println(sql);
                ResultSet rs = st.executeQuery(sql);
                while (rs.next())
                {
                    CampaignCPC.add(rs.getString("campaignid"));
                }
                rs.close();
                st.close();
            }
            catch (Exception ex)
            {
                Debug.println(ex.getMessage());
                Debug.println("Couldn't connect to the MySQL database 5.9. ");
            }
           
        
        return CampaignCPC;
    }
    
    
    public Map getBannerComplete(String date) throws InterruptedException
    {
            Map<String,String> BannerComplete= new HashMap<String,String>();
            try
            {
                Statement st= (Statement)conn.createStatement();
                String sql = "SELECT C.bannerid,A.campaignid FROM ox_campaigns A "+
                            "INNER JOIN selfserving_inform B "+
                            "ON B.id = A.campaignid " + 
                            "INNER JOIN ox_banners C ON C.campaignid = A.campaignid " +                          
                            "WHERE DATE = '"+date+"' AND B.type = 19 ";
                
                Debug.println(sql);
                ResultSet rs = st.executeQuery(sql);
                while (rs.next())
                {
                    BannerComplete.put(rs.getString("C.bannerid"),rs.getString("A.campaignid"));
                }
                rs.close();
                st.close();
            }
            catch (Exception ex)
            {
                Debug.println(ex.getMessage());
                Debug.println("Couldn't connect to the MySQL database 5.9. ");
            }
          
        
        return BannerComplete;
    }
    
    public Map getDataForPromotion(String date)
    {
        Map<String, String> MapData = new HashMap<>();
        try
        {
            Statement st= (Statement)conn.createStatement();
            
            String sql = "SELECT C.user_id,C.username,SUM(A.moneyc) AS money,SUM(B.maxValueDay) AS maxValueDay,C.balance,C.promotions,C.overdraft "+
                    "FROM v3_cam_date A  "+
                    "INNER JOIN ox_campaigns B ON A.campaignid = B.campaignid  "+
                    "INNER JOIN selfserving_users C ON C.user_id = B.uid  "+
                    "WHERE A.dt ='"+date+"' AND A.moneyc > 0 AND balance >0  "+
                    "GROUP BY C.user_id ORDER BY money DESC ";
            
            Debug.println(sql);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next())
            {
                MapData.put(rs.getString("C.user_id"), rs.getString("C.username")+","+rs.getString("money")+","+rs.getString("maxValueDay")+","+rs.getString("C.balance")+","+rs.getString("C.promotions")+","+rs.getString("C.overdraft"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 6.7. ");
        }
        return MapData;
    }
    
    public void deletePromotion(String date)
    {
        try {
            Statement s = conn.createStatement();
            String sql = "DELETE FROM promotions_admarket WHERE dt ='"+date+"'" ;
            //Debug.println(sql);
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 23.86.");
        }
    }
    
    public void loadToV3PromotionsFromFile(String filePath) throws Exception {

        try {
            String query = "LOAD DATA LOCAL INFILE '" + filePath + "'" +
                " INTO TABLE promotions_admarket FIELDS TERMINATED BY ',' " +
                "LINES TERMINATED BY ',\\n' (userid, username,money,balance,promotions,money_pro," +
                "money_sub,moneycharge,mn_balance,mn_promotion,overdraft,max_value_day," +
                "dt)";
            Debug.println(query);
            Statement s = conn.createStatement();
            s.executeQuery(query);           
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        }
    }

    
    public boolean checkLockTable(String tableName) throws Exception {
        boolean retValue = true;
        
        try {                                                                        
            Statement s = conn.createStatement();            
            String query = "SHOW OPEN TABLES WHERE In_use > 0";
            Debug.println(query);
            ResultSet rs = s.executeQuery(query);
            while (rs.next())
            {
                if(rs.getString("table") == null ? tableName == null : rs.getString("table").equals(tableName))
                {
                    retValue = false;
                    break;
                }
            }
            rs.close();
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        }
        return  retValue;
    }
    
    public void removeDataCampaignByDate(String date) throws Exception {
        Statement s = null;
        ResultSet resultSet = null;
        
        try {            
            String query = "DELETE FROM "+V3_CAM_DATE+" WHERE dt = '" + date + "'";
            Debug.println(query);
            s = conn.createStatement();            
            s.executeUpdate(query);
            //conn.commit();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        } finally {
            if (resultSet != null) {
                try {
                  resultSet.close();
                } catch (SQLException e) {
                } // nothing we can do
            }
            if (s != null) {
                try {
                  s.close();
                } catch (SQLException e) {
                } // nothing we can do
            }
         }
    }
    
    
    public void removeDataCampaignHourlyByDate(String date) throws Exception {
        Statement s = null;
        ResultSet resultSet = null;
        
        try {            
            String query = "DELETE FROM "+V3_CAM_HOURLY_CPC+" WHERE dt = '" + date + "'";
            Debug.println(query);
            s = conn.createStatement();            
            s.executeUpdate(query);
            //conn.commit();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        } finally {
            if (resultSet != null) {
                try {
                  resultSet.close();
                } catch (SQLException e) {
                } // nothing we can do
            }
            if (s != null) {
                try {
                  s.close();
                } catch (SQLException e) {
                } // nothing we can do
            }
         }
    }
    
    public void removeDataBanByDate(String date) throws Exception {
        Statement s = null;
        ResultSet resultSet = null;
        
        try {            
            String query = "DELETE FROM "+V3_BAN_DATE+" WHERE dt = '" + date + "'";
            Debug.println(query);
            s = conn.createStatement();            
            s.executeUpdate(query);
            //conn.commit();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        } finally {
            if (resultSet != null) {
                try {
                  resultSet.close();
                } catch (SQLException e) {
                } // nothing we can do
            }
            if (s != null) {
                try {
                  s.close();
                } catch (SQLException e) {
                } // nothing we can do
            }
         }
    }
    
    public void removeDataDomainByDate(String date) throws Exception {
        Statement s = null;
        ResultSet resultSet = null;
        
        try {            
            String query = "DELETE FROM "+V3_DOMAIN_DATE+" WHERE dt = '" + date + "'";
            Debug.println(query);
            s = conn.createStatement();            
            s.executeUpdate(query);
            //conn.commit();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        } finally {
            if (resultSet != null) {
                try {
                  resultSet.close();
                } catch (SQLException e) {
                } // nothing we can do
            }
            if (s != null) {
                try {
                  s.close();
                } catch (SQLException e) {
                } // nothing we can do
            }
         }
    }
    
    public void removeDataZoneByDate(String date) throws Exception {
        Statement s = null;
        ResultSet resultSet = null;
        
        try {            
            String query = "DELETE FROM "+V3_ZONE_DATE+" WHERE dt = '" + date + "'";
            Debug.println(query);
            s = conn.createStatement();            
            s.executeUpdate(query);
            //conn.commit();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        } finally {
            if (resultSet != null) {
                try {
                  resultSet.close();
                } catch (SQLException e) {
                } // nothing we can do
            }
            if (s != null) {
                try {
                  s.close();
                } catch (SQLException e) {
                } // nothing we can do
            }
         }
    }
    
    public void lockTable(String tablename) throws Exception {
        Statement s = null;
        ResultSet resultSet = null;
        
        try {            
            String query = "LOCK TABLE " + tablename + " WRITE";
            Debug.println(query);
            s = conn.createStatement();            
            s.executeQuery(query);
            //conn.commit();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        } finally {
            if (resultSet != null) {
                try {
                  resultSet.close();
                } catch (SQLException e) {
                } // nothing we can do
            }
            if (s != null) {
                try {
                  s.close();
                } catch (SQLException e) {
                } // nothing we can do
            }
         }
    }
    
    public void unlockTable() throws Exception {
        Statement s = null;
        ResultSet resultSet = null;
        
        try {            
            String query = "UNLOCK TABLES";
            Debug.println(query);
            s = conn.createStatement();            
            s.executeQuery(query);
            //conn.commit();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
          Debug.println("Couldn't connect to the MySQL database 6.7.");
        } finally {
            if (resultSet != null) {
                try {
                  resultSet.close();
                } catch (SQLException e) {
                } // nothing we can do
            }
            if (s != null) {
                try {
                  s.close();
                } catch (SQLException e) {
                } // nothing we can do
            }
         }
    }
    
    public int checkDataPromotionAdmarket(String date ){
        int retVal = 0 ;        
        try
        {
            Statement st= (Statement)conn.createStatement();          
            String sql ="SELECT COUNT(*) FROM promotions_admarket WHERE dt ='"+date+"'";
            Debug.println(sql);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next())
            {
                retVal = rs.getInt("COUNT(*)");
            }
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
            Debug.println("Couldn't connect to the MySQL database 5.9. ");
        }
        
        return  retVal;
    }
    
    
}
